/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pv168.uloha3.managers.impl;

import cz.muni.fi.pv168.uloha3.common.DBUtils;
import cz.muni.fi.pv168.uloha3.entities.Owner;
import cz.muni.fi.pv168.uloha3.entities.Ownership;
import cz.muni.fi.pv168.uloha3.entities.RealEstate;
import cz.muni.fi.pv168.uloha3.exceptions.OwnerManagerException;
import cz.muni.fi.pv168.uloha3.exceptions.OwnershipManagerException;
import cz.muni.fi.pv168.uloha3.exceptions.RealEstateManagerException;
import cz.muni.fi.pv168.uloha3.managers.OwnerManager;
import cz.muni.fi.pv168.uloha3.managers.OwnershipManager;
import cz.muni.fi.pv168.uloha3.managers.RealEstateManager;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author stefan
 */
public class OwnershipManagerImpl implements OwnershipManager {

    private static final Logger logger = Logger.getLogger(
            OwnershipManagerImpl.class.getName());
    
    private DataSource dataSource;
    
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }    

    private void checkDataSource() {
        if (dataSource == null) {
            throw new IllegalStateException("DataSource is not set");
        }
    }
    
    private OwnerManager ownerManager;
    private RealEstateManager realEstateManager;

    public void setOwnerManager(OwnerManager ownerManager) {
        this.ownerManager = ownerManager;
    }

    public void setRealEstateManager(RealEstateManager realEstateManager) {
        this.realEstateManager = realEstateManager;
    }
    
    
    static {
        try {
            OwnershipManagerImpl.class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException ex) {
            // what to do
        }
    }
    /*
    private static final String URL = "jdbc:derby://localhost:1527/pv168";
    private static final String USERNAME = "uzivatel";
    private static final String PASSWORD = "heslo";
    */
    
    private static final String PREPARED_STATEMENT_ADD_OWNERSHIP_STR = "INSERT INTO OWNERSHIP (REALESTATE_ID, OWNER_ID, DATE_CREATED, INVESTMENT) VALUES (?, ?, ?, ?)";
    private static final String PREPARED_STATEMENT_EDIT_OWNERSHIP_STR = "UPDATE OWNERSHIP SET REALESTATE_ID=?, OWNER_ID=?, DATE_CREATED=?, INVESTMENT=? WHERE ID=?";
    private static final String PREPARED_STATEMENT_END_OWNERSHIP_STR = "UPDATE OWNERSHIP SET DATE_ENDED=? WHERE ID=?";
    private static final String PREPARED_STATEMENT_GET_OWNERSHIPS_STR = "SELECT ID, REALESTATE_ID, OWNER_ID, DATE_CREATED, DATE_ENDED, INVESTMENT FROM OWNERSHIP";
    private static final String PREPARED_STATEMENT_GET_OWNERSHIP_BY_OWNER_STR = "SELECT ID, REALESTATE_ID, OWNER_ID, DATE_CREATED, DATE_ENDED, INVESTMENT FROM OWNERSHIP WHERE OWNER_ID=?";
    private static final String PREPARED_STATEMENT_GET_OWNERSHIP_BY_REALESTATE_STR = "SELECT ID, REALESTATE_ID, OWNER_ID, DATE_CREATED, DATE_ENDED, INVESTMENT FROM OWNERSHIP WHERE REALESTATE_ID=?";
    private static final String PREPARED_STATEMENT_GET_OWNERSHIP_BY_ID_STR = "SELECT ID, REALESTATE_ID, OWNER_ID, DATE_CREATED, DATE_ENDED, INVESTMENT FROM OWNERSHIP WHERE ID=?";
    
    
    public void createOwnerwhip(Ownership ownership) throws IllegalArgumentException, OwnershipManagerException {
        checkDataSource();
        if (ownership == null) {
            throw new IllegalArgumentException("Provided ownership cannot be null");
        }
        if (ownership.getOwner() == null) {
            throw new IllegalArgumentException("owner field cannot be null");
        }
        if (ownership.getRealEstate()== null) {
            throw new IllegalArgumentException("realEstate field cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_ADD_OWNERSHIP_STR, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setLong(1, ownership.getRealEstate().getId());
            preparedStatement.setLong(2, ownership.getOwner().getId());
            preparedStatement.setDate(3, ownership.getDateCreated());
            preparedStatement.setBigDecimal(4, ownership.getInvestment());
            
            preparedStatement.executeUpdate();
            
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            if (generatedKeys.next()) {
                Long ownershipId = generatedKeys.getLong(1);
                ownership.setId(ownershipId);
            } else {
                throw new OwnershipManagerException("No primary key generated for new Ownership " + ownership);
            }
            
            connection.commit();
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to add Ownership " + ownership + " to database: " + ex.getMessage(), ex);
        } finally {
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
        }
        
    }

    public void updateOwnership(Ownership ownership) throws IllegalArgumentException, OwnershipManagerException {
        if (ownership == null) {
            throw new IllegalArgumentException("Provided ownership cannot be null");
        }
        if (ownership.getId() == null) {
            throw new IllegalArgumentException("id field cannot be null");
        }
        if (ownership.getOwner() == null) {
            throw new IllegalArgumentException("owner field cannot be null");
        }
        if (ownership.getRealEstate()== null) {
            throw new IllegalArgumentException("realEstate field cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_EDIT_OWNERSHIP_STR);
            preparedStatement.setLong(1, ownership.getRealEstate().getId());
            preparedStatement.setLong(2, ownership.getOwner().getId());
            preparedStatement.setDate(3, ownership.getDateCreated());
            preparedStatement.setBigDecimal(4, ownership.getInvestment());
            
            preparedStatement.executeUpdate();
            
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to add Ownership " + ownership + " to database: " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
            
            /*
            if(preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            
            if(connection != null) {
                try {
                    connection.close();
                } catch (SQLException ex) {
                    logger.log(Level.WARNING, ex.getMessage());
                }
            }
            */
        }
        
    }

    public void endOwnership(Ownership ownership) throws IllegalArgumentException, OwnershipManagerException {
        if (ownership == null) {
            throw new IllegalArgumentException("Provided ownership cannot be null");
        }
        if(ownership.getId() == null) {
            throw new IllegalArgumentException("id field cannot be null");
        }
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_END_OWNERSHIP_STR);
            Date dateEnded = new Date(System.currentTimeMillis());
            preparedStatement.setDate(1, dateEnded);
            preparedStatement.setLong(2, ownership.getId());
            
            preparedStatement.executeUpdate();
            connection.commit();
            ownership.setDateEnded(dateEnded);
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to end Ownership " + ownership + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.doRollbackQuietly(connection);
            DBUtils.closeQuietly(connection, preparedStatement);
        }
        
    }

    public Set<Ownership> getOwnerships() throws OwnershipManagerException {
        
        Set<Ownership> result = new HashSet<Ownership>();
                
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNERSHIPS_STR);
            
            ResultSet resultSet = preparedStatement.executeQuery();
            connection.commit();
            
            result = parseResultSet(resultSet);
            
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to get all Ownerships: " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
        }
        
        return result;
    }

    public Set<Ownership> getOwnershipsByRealEstate(RealEstate realEstate) throws OwnershipManagerException {
        
        if(null == realEstate) {
            throw new IllegalArgumentException("RealEstate cannot be null");
        }
        if(null == realEstate.getId()) {
            throw new IllegalArgumentException("Parameter id cannot be null");
        }
        
        Set<Ownership> result = new HashSet<Ownership>();
                
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNERSHIP_BY_REALESTATE_STR);
            preparedStatement.setLong(1, realEstate.getId());
            
            ResultSet resultSet = preparedStatement.executeQuery();
            result = parseResultSet(resultSet);
            
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to get Ownerships for RealEstate " + realEstate + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
        }
        
        return result;
    }

    public Set<Ownership> getOwnershipsByOwner(Owner owner) throws OwnershipManagerException {
        
        if(null == owner) {
            throw new IllegalArgumentException("Owner cannot be null");
        }
        if(null == owner.getId()) {
            throw new IllegalArgumentException("Parameter id cannot be null");
        }
        
        Set<Ownership> result = new HashSet<Ownership>();
                
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNERSHIP_BY_OWNER_STR);
            preparedStatement.setLong(1, owner.getId());
            
            ResultSet resultSet = preparedStatement.executeQuery();
            result = parseResultSet(resultSet);
            
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to get Ownerships for Owner " + owner + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
        }
        
        return result;
    }

    public Ownership getOwnershipById(Long id) throws IllegalArgumentException, OwnershipManagerException {
        
        if(null == id) {
            throw new IllegalArgumentException("Parameter id cannot be null");
        }
        
        Ownership result = null;
                
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            
            preparedStatement = connection.prepareStatement(PREPARED_STATEMENT_GET_OWNERSHIP_BY_ID_STR);
            preparedStatement.setLong(1, id);
            
            ResultSet resultSet = preparedStatement.executeQuery();
            result = parseResultSet(resultSet).iterator().next();
            
        } catch (SQLException ex) {
            throw new OwnershipManagerException("SQLException caught while trying to get Ownerships with id " + id + ": " + ex.getMessage(), ex);
        } finally {
            DBUtils.closeQuietly(connection, preparedStatement);
        }
        
        return result;
    }

    private Set<Ownership> parseResultSet(ResultSet resultSet) throws SQLException, OwnershipManagerException {
        Set<Ownership> result = new HashSet<Ownership>();
    
        while(resultSet.next()) {
            Ownership ownership = new Ownership();
            
            ownership.setId(resultSet.getLong(1));
            
            
            Long realEstateId = resultSet.getLong(2);
            try {
                RealEstate realEstate = realEstateManager.getRealEstateById(realEstateId);
                ownership.setRealEstate(realEstate);
            } catch (RealEstateManagerException ex) {
                throw new OwnershipManagerException(ex);
            }

            Long ownerId = resultSet.getLong(3);
            try {
                Owner owner = ownerManager.getOwnerById(ownerId);
                ownership.setOwner(owner);
            } catch (OwnerManagerException ex) {
                throw new OwnershipManagerException(ex);
            }
            
            Date dateCreated = resultSet.getDate(4);
            ownership.setDateCreated(dateCreated);
            
            // skip ownerships that ended
            Date dateEnded = resultSet.getDate(5);
            if(null != dateEnded) continue;
            
            ownership.setInvestment(resultSet.getBigDecimal(6));
            
            result.add(ownership);
        }
        return result;
    }
}
